﻿Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports External.APICalling
Imports BusinessRule.DTO
Imports System.Configuration

Public Class uMESSecurityAuditBusiness
    Implements uMESSecurityAuditIBusiness
    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()

    Private Function GetModelAuditReport(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult _
                    Implements uMESSecurityAuditIBusiness.GetModelAuditReport

        Dim oResult As New uMESProcessResult

        Try
            Dim strFactoryName, strEmployeeName, strStartDate, strEndDate, _
                 strManipulateObject, strManipulateType, strManipulateResult As String

            strFactoryName = ""
            If QueryCondition.Keys.Contains("FactoryName") = True Then
                strFactoryName = QueryCondition("FactoryName").ToString()
            End If

            strEmployeeName = ""
            If QueryCondition.Keys.Contains("EmployeeName") = True Then
                strEmployeeName = QueryCondition("EmployeeName").ToString()
            End If

            strStartDate = ""
            If QueryCondition.Keys.Contains("StartDate") = True Then
                strStartDate = QueryCondition("StartDate").ToString()
            End If

            strEndDate = ""
            If QueryCondition.Keys.Contains("EndDate") = True Then
                strEndDate = QueryCondition("EndDate").ToString()
            End If

            strManipulateObject = ""
            If QueryCondition.Keys.Contains("ManipulateObject") = True Then
                strManipulateObject = QueryCondition("ManipulateObject").ToString()
            End If

            strManipulateType = ""
            If QueryCondition.Keys.Contains("ManipulateType") = True Then
                strManipulateType = QueryCondition("ManipulateType").ToString()
            End If

            strManipulateResult = ""
            If QueryCondition.Keys.Contains("ManipulateResult") = True Then
                strManipulateResult = QueryCondition("ManipulateResult").ToString()
            End If

            Dim strSQL As String = ""
            strSQL = "SELECT DISTINCT Modelingaudittrailheader.Objectname,Factory.Factoryname,  Modelingaudittrail.Objecttypename," & vbCrLf & _
                 "       DECODE(Modelingaudittrailheader.Executeactionid,'1','新增','2','修改','3','删除','4','新增版本','5','完全删除') AS TxnType, " & vbCrLf & _
                 "       Employee.Employeename AS EmployeeName ,Modelingaudittrailheader.Txndate, '成功' as ResultDetail " & vbCrLf & _
                 "FROM Modelingaudittrailheader" & vbCrLf & _
                 "INNER JOIN Modelingaudittrail ON Modelingaudittrail.Modelingaudittrailheaderid = Modelingaudittrailheader.Modelingaudittrailheaderid" & vbCrLf & _
                 "INNER JOIN Employee ON Employee.Employeeid = Modelingaudittrailheader.Employeeid" & vbCrLf & _
                 "LEFT JOIN Sessionvalues ON Sessionvalues.Employeeid = Employee.Employeeid" & vbCrLf & _
                 "LEFT JOIN Factory ON Factory.Factoryid = Sessionvalues.Factoryid" & vbCrLf & _
                 "WHERE 1 = 1 " & vbCrLf
            ' "WHERE 1 = 1 AND ( Modelingaudittrailheader.Objecttypename = 'Workflow' OR Modelingaudittrailheader.Objecttypename = 'Document')" & vbCrLf

            If strManipulateObject <> "" Then
                strSQL += "AND Modelingaudittrailheader.Objectname like '%" & strManipulateObject & "%'" & vbCrLf
            End If
            If strManipulateType <> "" And strManipulateType <> "0" Then
                strSQL += "AND Modelingaudittrailheader.Executeactionid = '" & strManipulateType & "'" & vbCrLf
            End If
            'If strManipulateResult <> "" Then
            '    strSQL += "AND ResultDetail like '%" & strManipulateResult & "%'" & vbCrLf
            'End If

            If strFactoryName <> "" Then
                strSQL += "AND Factory.FactoryName = '" & strFactoryName & "'" & vbCrLf
            End If
            If strEmployeeName <> "" Then
                strSQL += "AND Employee.Employeename like '%" & strEmployeeName & "%'" & vbCrLf
            End If
            If strStartDate <> "" Then
                strSQL += "AND Modelingaudittrailheader.Txndate >= TO_Date('" & strStartDate & "','RRRR-MM-DD HH24:MI:SS')" & vbCrLf
            End If
            If strEndDate <> "" Then
                strSQL += "AND Modelingaudittrailheader.Txndate <= TO_Date('" & strEndDate & "','RRRR-MM-DD HH24:MI:SS')" & vbCrLf
            End If

            strSQL += "ORDER BY Modelingaudittrailheader.Txndate DESC" & vbCrLf

            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
            paraPage.strSQL = strSQL

            oResult = dal.RunProcedureForPage(paraPage)

            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    Private Function GetManagerHistoryReport(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult _
                Implements uMESSecurityAuditIBusiness.GetManagerHistoryReport
        Dim oResult As New uMESProcessResult

        Try
            Dim strFactoryName, strEmployeeName, strStartDate, strEndDate As String

            strFactoryName = ""
            If QueryCondition.Keys.Contains("FactoryName") = True Then
                strFactoryName = QueryCondition("FactoryName").ToString()
            End If

            strEmployeeName = ""
            If QueryCondition.Keys.Contains("EmployeeName") = True Then
                strEmployeeName = QueryCondition("EmployeeName").ToString()
            End If

            strStartDate = ""
            If QueryCondition.Keys.Contains("StartDate") = True Then
                strStartDate = QueryCondition("StartDate").ToString()
            End If

            strEndDate = ""
            If QueryCondition.Keys.Contains("EndDate") = True Then
                strEndDate = QueryCondition("EndDate").ToString()
            End If

            Dim strSQL As String = ""
            strSQL = "SELECT DISTINCT Modelingaudittrailheader.Objectname,Factory.Factoryname,  Modelingaudittrail.Objecttypename," & vbCrLf & _
                 "       DECODE(Modelingaudittrailheader.Executeactionid,'1','新增','2','修改','3','删除','4','新增版本','5','完全删除') AS TxnType, " & vbCrLf & _
                 "       Employee.Employeename AS EmployeeName ,Modelingaudittrailheader.Txndate, '成功' as ResultDetail " & vbCrLf & _
                 "FROM Modelingaudittrailheader" & vbCrLf & _
                 "INNER JOIN Modelingaudittrail ON Modelingaudittrail.Modelingaudittrailheaderid = Modelingaudittrailheader.Modelingaudittrailheaderid" & vbCrLf & _
                 "INNER JOIN Employee ON Employee.Employeeid = Modelingaudittrailheader.Employeeid" & vbCrLf & _
                 "LEFT JOIN Sessionvalues ON Sessionvalues.Employeeid = Employee.Employeeid" & vbCrLf & _
                 "LEFT JOIN Factory ON Factory.Factoryid = Sessionvalues.Factoryid" & vbCrLf & _
                 "LEFT JOIN Employeerole ON Employeerole.Employeeid = Employee.Employeeid" & vbCrLf & _
                 "LEFT JOIN RoleDef ON RoleDef.RoleID = Employeerole.Roleid" & vbCrLf & _
                 "WHERE (RoleDef.Rolename = 'secAdmin_Role' OR RoleDef.Rolename = 'sysAdmin_Role')" & vbCrLf

            If strFactoryName <> "" Then
                strSQL += "AND Factory.FactoryName = '" & strFactoryName & "'" & vbCrLf
            End If
            If strEmployeeName <> "" Then
                strSQL += "AND Employee.Employeename = '" & strEmployeeName & "'" & vbCrLf
            End If
            If strStartDate <> "" Then
                strSQL += "AND Modelingaudittrailheader.Txndate >= TO_Date('" & strStartDate & "','RRRR-MM-DD HH24:MI:SS')" & vbCrLf
            End If
            If strEndDate <> "" Then
                strSQL += "AND Modelingaudittrailheader.Txndate <= TO_Date('" & strEndDate & "','RRRR-MM-DD HH24:MI:SS')" & vbCrLf
            End If

            strSQL += "ORDER BY Modelingaudittrailheader.Txndate DESC" & vbCrLf

            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
            paraPage.strSQL = strSQL

            oResult = dal.RunProcedureForPage(paraPage)
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    Private Function GetEmployeeManageReport(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult _
                Implements uMESSecurityAuditIBusiness.GetEmployeeManageReport
        Dim oResult As New uMESProcessResult

        Try
            Dim strFactoryName, strEmployeeName, strStartDate, strEndDate As String

            strFactoryName = ""
            If QueryCondition.Keys.Contains("FactoryName") = True Then
                strFactoryName = QueryCondition("FactoryName").ToString()
            End If

            strEmployeeName = ""
            If QueryCondition.Keys.Contains("EmployeeName") = True Then
                strEmployeeName = QueryCondition("EmployeeName").ToString()
            End If

            strStartDate = ""
            If QueryCondition.Keys.Contains("StartDate") = True Then
                strStartDate = QueryCondition("StartDate").ToString()
            End If

            strEndDate = ""
            If QueryCondition.Keys.Contains("EndDate") = True Then
                strEndDate = QueryCondition("EndDate").ToString()
            End If

            Dim strSQL As String = ""
            strSQL = "SELECT DISTINCT UseEmployee.employeename AS UseEmployeeName,Factory.Factoryname, " & vbCrLf & _
                     "       DECODE(Modelingaudittrailheader.Executeactionid,'1','新增','2','修改','3','删除','4','新增版本','5','完全删除') AS TxnType, " & vbCrLf & _
                     "       Employee.Employeename AS EmployeeName ,Modelingaudittrailheader.Txndate, '成功' as ResultDetail " & vbCrLf & _
                     "FROM Modelingaudittrailheader" & vbCrLf & _
                     "INNER JOIN Modelingaudittrail ON Modelingaudittrail.Modelingaudittrailheaderid = Modelingaudittrailheader.Modelingaudittrailheaderid" & vbCrLf & _
                     "INNER JOIN Employee ON Employee.Employeeid = Modelingaudittrailheader.Employeeid" & vbCrLf & _
                     "INNER JOIN Employee UseEmployee ON UseEmployee.Employeeid = Modelingaudittrailheader.Objectinstanceid" & vbCrLf & _
                     "LEFT JOIN Sessionvalues ON Sessionvalues.Employeeid = UseEmployee.Employeeid" & vbCrLf & _
                     "LEFT JOIN Factory ON Factory.Factoryid = Sessionvalues.Factoryid" & vbCrLf & _
                     "WHERE Modelingaudittrailheader.Objecttypename ='Employee'" & vbCrLf

            If strFactoryName <> "" Then
                strSQL += "AND Factory.FactoryName = '" & strFactoryName & "'" & vbCrLf
            End If
            If strEmployeeName <> "" Then
                strSQL += "AND Employee.Employeename = '" & strEmployeeName & "'" & vbCrLf
            End If
            If strStartDate <> "" Then
                strSQL += "AND Modelingaudittrailheader.Txndate >= TO_Date('" & strStartDate & "','RRRR-MM-DD HH24:MI:SS')" & vbCrLf
            End If
            If strEndDate <> "" Then
                strSQL += "AND Modelingaudittrailheader.Txndate <= TO_Date('" & strEndDate & "','RRRR-MM-DD HH24:MI:SS')" & vbCrLf
            End If

            strSQL += "ORDER BY Modelingaudittrailheader.Txndate DESC" & vbCrLf

            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
            paraPage.strSQL = strSQL

            oResult = dal.RunProcedureForPage(paraPage)
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    Private Function GetEmployeePurviewReport(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult _
                Implements uMESSecurityAuditIBusiness.GetEmployeePurviewReport
        Dim oResult As New uMESProcessResult

        Try
            Dim strFactoryName, strEmployeeName, strStartDate, strEndDate As String

            strFactoryName = ""
            If QueryCondition.Keys.Contains("FactoryName") = True Then
                strFactoryName = QueryCondition("FactoryName").ToString()
            End If

            strEmployeeName = ""
            If QueryCondition.Keys.Contains("EmployeeName") = True Then
                strEmployeeName = QueryCondition("EmployeeName").ToString()
            End If

            strStartDate = ""
            If QueryCondition.Keys.Contains("StartDate") = True Then
                strStartDate = QueryCondition("StartDate").ToString()
            End If

            strEndDate = ""
            If QueryCondition.Keys.Contains("EndDate") = True Then
                strEndDate = QueryCondition("EndDate").ToString()
            End If

            Dim strSQL As String = ""
            strSQL = "SELECT DISTINCT UseEmployee.employeename AS UseEmployeeName,Factory.Factoryname,  Modelingaudittrail.Objecttypename," & vbCrLf & _
                "       DECODE(Modelingaudittrailheader.Executeactionid,'1','新增','2','修改','3','删除','4','新增版本','5','完全删除') AS TxnType, " & vbCrLf & _
                "       Employee.Employeename AS EmployeeName ,Modelingaudittrailheader.Txndate, '成功' as ResultDetail " & vbCrLf & _
                "FROM Modelingaudittrailheader" & vbCrLf & _
                "INNER JOIN Modelingaudittrail ON Modelingaudittrail.Modelingaudittrailheaderid = Modelingaudittrailheader.Modelingaudittrailheaderid" & vbCrLf & _
                "INNER JOIN Employee ON Employee.Employeeid = Modelingaudittrailheader.Employeeid" & vbCrLf & _
                "INNER JOIN Employee UseEmployee ON UseEmployee.Employeeid = Modelingaudittrailheader.Objectinstanceid" & vbCrLf & _
                "LEFT JOIN Sessionvalues ON Sessionvalues.Employeeid = UseEmployee.Employeeid" & vbCrLf & _
                "LEFT JOIN Factory ON Factory.Factoryid = Sessionvalues.Factoryid" & vbCrLf & _
                "WHERE 1=1 " & vbCrLf

            If strFactoryName <> "" Then
                strSQL += "AND Factory.FactoryName = '" & strFactoryName & "'" & vbCrLf
            End If
            If strEmployeeName <> "" Then
                strSQL += "AND Employee.Employeename = '" & strEmployeeName & "'" & vbCrLf
            End If
            If strStartDate <> "" Then
                strSQL += "AND Modelingaudittrailheader.Txndate >= TO_Date('" & strStartDate & "','RRRR-MM-DD HH24:MI:SS')" & vbCrLf
            End If
            If strEndDate <> "" Then
                strSQL += "AND Modelingaudittrailheader.Txndate <= TO_Date('" & strEndDate & "','RRRR-MM-DD HH24:MI:SS')" & vbCrLf
            End If

            strSQL += "ORDER BY Modelingaudittrailheader.Txndate DESC" & vbCrLf

            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
            paraPage.strSQL = strSQL

            oResult = dal.RunProcedureForPage(paraPage)
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    Private Function GetEmployeeSecretLevel(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult _
               Implements uMESSecurityAuditIBusiness.GetEmployeeSecretLevel
        Dim oResult As New uMESProcessResult

        Try
            Dim strUserName As String
            strUserName = ""
            If QueryCondition.Keys.Contains("UserName") = True Then
                strUserName = QueryCondition("UserName").ToString()
            End If
           

            Dim strSQL As String = ""
            strSQL = "select  employee.secretlevel from employee where UPPER(Employee.Employeename) =UPPER('" & strUserName & "')"

            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
            paraPage.strSQL = strSQL

            oResult = dal.RunProcedureForPage(paraPage)
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

End Class
